﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace LoggingQueue
{
    public class SqlReflectionStore<T>
    {
        public Type GItemDataType() { return typeof(T); }
        public bool SupportsType(Type t) { return this.GItemDataType() == t; }
        string dataServiceKey = "_dsk";
        private static bool isFailingOver;
        private List<string> columns = new List<string>();
        public string GTableName()
        {
            return typeof(T).FullName ?? typeof(T).Name;
        }

        /// <summary>
        /// returns an opened SqlConnection that is ensured to work, or failover connection
        /// </summary>
        /// <returns></returns>
        protected SqlConnection GetSqlConnection(string ConnectionString)
        {
            SqlConnection conn;
            if (isFailingOver && !string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings["FailoverConnection"].ConnectionString))
            {
                conn = new SqlConnection(ConfigurationManager.ConnectionStrings["FailoverConnection"].ConnectionString);
            }
            else
            {
                conn = new SqlConnection(ConnectionString);
            }

            try
            {
                conn.Open();
            }
            catch (SqlException ex)
            {
               
                isFailingOver = !isFailingOver;

                if (isFailingOver)
                {
                    conn = new SqlConnection(ConfigurationManager.ConnectionStrings["FailoverConnection"].ConnectionString);
                    conn.Open();
                }
                else
                {
                    conn = new SqlConnection(ConnectionString);
                    conn.Open();
                }
            }

            return conn;
        }


        internal bool Add(object serviceObject)
        {
            if (serviceObject == null) { return false; }
            if (serviceObject.GetType() != typeof(T)) { return false; }
            var tablename = this.GTableName();

            var props = new List<SqlParameter>();
            var fields = new List<string>();
            var nulls = new List<string>();
            foreach (var column in this.columns)
            {
                var nativeProp = typeof(T).GetProperty(column);
                if (nativeProp == null || !nativeProp.CanRead) { continue; }
                var val = nativeProp.GetValue(serviceObject, null);

                if (val == null || (val is DateTime && (DateTime)val == DateTime.MinValue))
                {
                    nulls.Add(column);
                    continue;
                }
                props.Add(new SqlParameter("@" + column, val));
                fields.Add(column);
            }

            using (var con = GetSqlConnection(ConfigurationManager.ConnectionStrings["WriteConnection"].ConnectionString))
            {
                fields.Add(dataServiceKey);
                string sql = "INSERT INTO [" + tablename + "] (" + string.Join(", ", fields.Select(f => "[" + f + "]")) + ") VALUES (" +
                    string.Join(", ", fields.Select(f => "@" + f)) + ")";
                var cmd = new SqlCommand(sql, con);

                if (!props.Any(p => p.ParameterName.Equals(dataServiceKey)))
                {
                    props.Add(new SqlParameter("@" + dataServiceKey, Guid.NewGuid().ToString()));
                }
                cmd.Parameters.AddRange(props.ToArray());
                if (cmd.ExecuteNonQuery() > 0) { return true; }

            }
            return false;
        }

        public void InitSQL()
        {
            var tablename = this.GTableName();

            using (var con = GetSqlConnection(ConfigurationManager.ConnectionStrings["WriteConnection"].ConnectionString))
            {
                string tableExistsQuery = @"SELECT COUNT(*) FROM " +
                    con.Database + ".INFORMATION_SCHEMA.TABLES " +
                    "WHERE TABLE_NAME = '" + tablename + "'";
                var cmd = new SqlCommand(tableExistsQuery, con);
                int count = Int32.Parse(cmd.ExecuteScalar().ToString());
                if (count < 1)
                {
                    // create table
                    new SqlCommand("CREATE TABLE [" + tablename + "] ([" + dataServiceKey + "] VARCHAR(100) NOT NULL, PRIMARY KEY ([" + dataServiceKey + "]), [Created] DATETIME DEFAULT GETDATE())", con).ExecuteNonQuery();

                }
                string sql = @"SELECT TOP 0 * from [" + tablename + "]";
                cmd = new SqlCommand(sql, con);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                using (reader)
                {
                    var schema = reader.GetSchemaTable();
                    foreach (DataRow row in schema.Rows)
                    {
                        columns.Add(row["ColumnName"].ToString());

                    }
                }
                var merger = new SchemaMerge();
                foreach (var prop in typeof(T).GetProperties())
                {
                    //ignore read or write only props as well as collections
                    if (prop.CanRead && prop.CanWrite
                        && !columns.Contains(prop.Name) && merger.Mappings.ContainsKey(prop.PropertyType))
                    {
                        var dbt = merger.Mappings[prop.PropertyType];
                        var s = "ALTER TABLE [" + tablename + "] ADD [" + prop.Name + "] " + dbt.ToString().ToLower() + ";";
                        s = s.Replace("varchar;", "varchar(MAX);");
                        var create = new SqlCommand(s, con);
                        create.ExecuteNonQuery();
                        columns.Add(prop.Name);

                    }
                }

            }
        }

        class SchemaMerge
        {
            private static Dictionary<Type, SqlDbType> mapping = new Dictionary<Type, SqlDbType>();
            static SchemaMerge()
            {
                mapping.Add(typeof(byte), SqlDbType.TinyInt);
                mapping.Add(typeof(bool), SqlDbType.Bit);
                mapping.Add(typeof(byte[]), SqlDbType.Image);
                mapping.Add(typeof(DateTime), SqlDbType.DateTime);
                mapping.Add(typeof(Decimal), SqlDbType.Decimal);
                mapping.Add(typeof(double), SqlDbType.Float);
                mapping.Add(typeof(Guid), SqlDbType.UniqueIdentifier);
                mapping.Add(typeof(Int16), SqlDbType.SmallInt);
                mapping.Add(typeof(Int32), SqlDbType.Int);
                mapping.Add(typeof(Int64), SqlDbType.BigInt);
                mapping.Add(typeof(object), SqlDbType.Variant);
                mapping.Add(typeof(string), SqlDbType.VarChar);
            }

            public Dictionary<Type, SqlDbType> Mappings { get { return mapping; } }

        }

    }
}
